﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using CodeCreator.CoreObj;
using CodeCreator.Template;
using MySql.Data.MySqlClient;
using CodeCreator.CoreObj.DbManager;

namespace CodeCreator.DbHelper
{
    public class DbHelperForMySql : IDbHelper
    {
        private string connectionString = "";
        public DbHelperForMySql(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public int ExecuteSql(string SQLString)
        {
            using (connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (MySql.Data.MySqlClient.MySqlException E)
                    {
                        connection.Close();
                        throw new Exception(E.Message);
                    }
                }
            }
        }

        public object GetSingle(string SQLString)
        {
            using (connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (MySql.Data.MySqlClient.MySqlException e)
                    {
                        connection.Close();
                        throw new Exception(e.Message);
                    }
                }
            }
        }

        public IDataReader ExecuteReader(string strSQL)
        {
            connection = new MySqlConnection(connectionString);
            MySqlCommand cmd = new MySqlCommand(strSQL, connection);
            try
            {
                connection.Open();
                IDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (MySql.Data.MySqlClient.MySqlException e)
            {
                throw new Exception(e.Message);
            }

        }

        public DataSet Query(string SQLString)
        {
            using (connection = new MySqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (MySql.Data.MySqlClient.MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }

        MySqlConnection connection = null;
        public void CloseConnection()
        {
            try
            {
                if (this.connection != null && this.connection.State == ConnectionState.Open)
                {
                    this.connection.Close();
                }
            }
            catch
            { }
        }

        private string objectToString(object obj)
        {
            if (obj != null)
                return obj.ToString();
            else
                return "";
        }  

        public List<string> GetAllSchema()
        {
            string sql = "select SCHEMA_NAME from information_schema.SCHEMATA";
            List<string> result = new List<string>();
            DataSet ds = Query(sql);
            if (ds != null && ds.Tables.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    result.Add(this.objectToString(dr["SCHEMA_NAME"]));
                }
            }
            return result;
        }

        public List<string> GetTablesBySchema(string schemaName)
        {
            string sql = "select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='"+schemaName+"'";
            List<string> result = new List<string>();
            DataSet ds = Query(sql);
            if (ds != null && ds.Tables.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    result.Add(this.objectToString(dr["TABLE_NAME"]));
                }
            }
            return result;
        }

        public Obj GetColumnsByTable(string schemaName, string tableName, List<TypeMap> typeMapList)
        {
            Obj obj = new Obj();
            obj.TableFullName = schemaName + "." + tableName;
            obj.ClassName = Common.DbFirstToUpper(tableName);
            obj.PackageName = "";

            string strSql = "select ";
            strSql += " c.COLUMN_NAME,c.DATA_TYPE,c.CHARACTER_MAXIMUM_LENGTH as DATA_LENGTH, ";
            strSql += " case when k.CONSTRAINT_NAME='PRIMARY' then 1 else 0 end as ISPK ";
            strSql += " from information_schema.COLUMNS  c left join information_schema.KEY_COLUMN_USAGE k ";
            strSql += " on c.TABLE_NAME=k.TABLE_NAME and c.TABLE_SCHEMA= k.TABLE_SCHEMA and c.COLUMN_NAME= k.COLUMN_NAME ";
            strSql += " where c.TABLE_SCHEMA='"+schemaName+"' and c.table_name='"+tableName+"'";
            //构建表的字段
            int pkCount = 0;
            string pkType = "";
            DataSet ds = Query(strSql);

            if (ds != null && ds.Tables.Count > 0)
            {
                List<ObjField> fields = new List<ObjField>();
                foreach (DataRow dc in ds.Tables[0].Rows)
                {
                    ObjField of = new ObjField();
                    of.DbFieldName = objectToString(dc["COLUMN_NAME"]);
                    of.DbFieldType = objectToString(dc["DATA_TYPE"]);
                    of.DbFieldLength = objectToString(dc["DATA_LENGTH"]);
                    TypeMap tm = Common.GetTypeMapByDb(typeMapList, of.DbFieldType);
                    if (tm != null)
                    {
                        of.ClassFieldType = tm.ClassType;
                        of.JsFieldType = tm.JsType;
                    }
                    else
                    {
                        of.ClassFieldType = objectToString(dc["DATA_TYPE"]);
                        of.JsFieldType = objectToString(dc["DATA_TYPE"]);
                    }

                    if (objectToString(dc["ISPK"]) == "1")
                    {
                        of.DbFieldPk = true;
                        pkCount += 1;
                        pkType = of.DbFieldType;
                    }
                    fields.Add(of);
                }
                obj.Fields = fields;

                //当未设置主键时，可手工指定
                if (pkCount < 1)
                {
                    FrmGetPk frmGetPk = new FrmGetPk();
                    frmGetPk.TableName = obj.TableFullName;
                    frmGetPk.Fields = fields;
                    if (frmGetPk.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                    {
                        pkCount = 1;
                        pkType = "NUMBER";
                    }
                }
            }

            //当主键只有一个且为NUMBER类型时才引用Sequence
            if (pkCount == 1)// && pkType.ToUpper() == "NUMBER")
            {
                foreach (ObjField of in obj.Fields)
                {
                    if (of.DbFieldPk)
                    {
                        of.DbSequence = schemaName + ".SEQ_" + tableName;
                        break;
                    }
                }
            }

            return obj;
        }
    }
}
